/*
 * @Description: JDBC测试
 * @Author: rendc
 * @Date: 2024-11-18 16:58:20
 * @LastEditors: rendc
 * @LastEditTime: 2024-11-18 17:29:33
 */
package com.briup;

import org.junit.Test;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.Arrays;

public class JdbcTest {
    // 数据库连接四要素
    // 1. 驱动
    String driver = "com.mysql.cj.jdbc.Driver";
    // 2. 数据库地址
    String url = "jdbc:mysql://127.0.0.1:3306/briup";
    // 3. 用户名 - 写自己数据库的用户名
    String username = "briup";
    // 4. 密码 - 写自己数据库的密码
    String password = "briup";

    // 获取数据库连接 并输出连接对象
    @Test
    public void getConnection() {
        Connection conn = null;
        try {
            // 1. 注册驱动
            Class.forName(driver);
            // 2. 获取连接对象
            conn = DriverManager.getConnection(url, username, password);
            // 输出连接对象
            System.out.println(conn);
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            // 6. 释放资源
            if (conn != null) {
                try {
                    conn.close();
                } catch (Exception e) {
                    e.printStackTrace();
                }
            }
        }
    }

    // 删除表
    @Test
    public void dropTable() {
        // 数据库连接对象
        Connection conn = null;
        // 数据库操作对象
        Statement stmt = null;
        try {
            // 1. 加载驱动
            Class.forName(driver);
            // 2. 获取连接对象
            conn = DriverManager.getConnection(url, username, password);
            // 3. 获取操作对象
            stmt = conn.createStatement();
            // 4. 执行sql语句
            int rows = stmt.executeUpdate("DROP TABLE IF EXISTS t_user");
            // 5. 处理结果
            System.out.println("返回结果rows：" + rows);
        } catch (Exception e) {
            // 释放资源
            if (stmt != null) {
                try {
                    stmt.close();
                } catch (Exception err) {
                    err.printStackTrace();
                }
            }
            if (conn != null) {
                try {
                    conn.close();
                } catch (Exception err) {
                    err.printStackTrace();
                }
            }
        }
    }

    // 创建表
    @Test
    public void createTable() {
        // 数据库连接对象
        Connection conn = null;
        // 数据库操作对象
        Statement stmt = null;
        try {
            // 1. 加载驱动
            Class.forName(driver);
            // 2. 获取连接对象
            conn = DriverManager.getConnection(url, username, password);
            // 3. 获取操作对象
            stmt = conn.createStatement();
            // 4. 执行sql语句
            String sql = "create table t_user("
                    + "id INTEGER PRIMARY KEY,"
                    + "name VARCHAR(50) NOT NULL,"
                    + "age INTEGER"
                    + ")";
            int rows = stmt.executeUpdate(sql);
            // 5. 处理结果
            System.out.println("返回结果rows：" + rows);
        } catch (Exception e) {
            // 释放资源
            if (stmt != null) {
                try {
                    stmt.close();
                } catch (Exception err) {
                    err.printStackTrace();
                }
            }
            if (conn != null) {
                try {
                    conn.close();
                } catch (Exception err) {
                    err.printStackTrace();
                }
            }
        }
    }

    // 插入数据
    @Test
    public void insertData() {
        Connection conn = null;
        Statement stmt = null;
        try {
            // 1. 加载驱动
            Class.forName(driver);
            // 2. 获取连接对象
            conn = DriverManager.getConnection(url, username, password);
            // 3. 获取操作对象
            stmt = conn.createStatement();
            // 4. 执行sql语句
            String sql1 = "insert into t_user(id,name,password,age) values(4,'tom','tom',20)";
            String sql2 = "insert into t_user(id,name,password,age) values(5,'admin','admin',19)";
            String sql3 = "insert into t_user(id,name,password,age) values(6,'briup','briup',18)";
            // int rows = stmt.executeUpdate(sql);
            // 执行多个sql语句
            stmt.addBatch(sql1);
            stmt.addBatch(sql2);
            stmt.addBatch(sql3);
            // 开始处理
            int[] rows = stmt.executeBatch();
            // 5. 处理结果
            System.out.println("修改的行数 rows：" + Arrays.toString(rows));
        } catch (Exception e) {
            // 6. 释放资源
            if (stmt != null) {
                try {
                    stmt.close();
                } catch (Exception exc) {
                    exc.printStackTrace();
                }
            }
            if (conn != null) {
                try {
                    conn.close();
                } catch (Exception exc) {
                    exc.printStackTrace();
                }
            }
        }
    }

    // 更新数据
    @Test
    public void updateData() {
        Connection conn = null;
        Statement stmt = null;
        try {
            // 1. 加载驱动
            Class.forName(driver);
            // 2. 获取连接对象
            conn = DriverManager.getConnection(url, username, password);
            // 3. 获取操作对象
            stmt = conn.createStatement();
            // 4. 执行sql语句
            String sql = "update t_user set name = '张三' where id = 1";
            int rows = stmt.executeUpdate(sql);
            // 5. 处理结果
            System.out.println("修改的行数 rows：" + rows);
        } catch (Exception e) {
            // 6. 释放资源
            if (stmt != null) {
                try {
                    stmt.close();
                } catch (Exception exc) {
                    exc.printStackTrace();
                }
            }
            if (conn != null) {
                try {
                    conn.close();
                } catch (Exception exc) {
                    exc.printStackTrace();
                }
            }
        }
    }

    // 删除数据
    @Test
    public void deleteData() {
        Connection conn = null;
        Statement stmt = null;
        try {
            // 1. 加载驱动
            Class.forName(driver);
            // 2. 获取连接对象
            conn = DriverManager.getConnection(url, username, password);
            // 3. 获取操作对象
            stmt = conn.createStatement();
            // 4. 执行sql语句
            String sql = "delete from t_user where id = 2";
            int rows = stmt.executeUpdate(sql);
            // 5. 处理结果
            System.out.println("修改的行数 rows：" + rows);
        } catch (Exception e) {
            // 6. 释放资源
            if (stmt != null) {
                try {
                    stmt.close();
                } catch (Exception exc) {
                    exc.printStackTrace();
                }
            }
            if (conn != null) {
                try {
                    conn.close();
                } catch (Exception exc) {
                    exc.printStackTrace();
                }
            }
        }
    }

    // 查询user表中的所有数据
    @Test
    public void getData() {
        Connection conn = null;
        Statement stmt = null;
        ResultSet rs = null;
        try {
            // 1. 加载驱动
            Class.forName(driver);
            // 2. 获取连接对象
            conn = DriverManager.getConnection(url, username, password);
            // 3. 获取操作对象
            stmt = conn.createStatement();
            // 4. 执行sql语句
            String sql = "select * from t_user";
            rs = stmt.executeQuery(sql); // 注意这里执行的是查询语句
            // 5. 处理结果
            while (rs.next()) {
                // 通过id获取数据（列名）
                // int id = rs.getInt("id");
                // String name = rs.getString("name");
                // System.out.println("id: " + id+",name:"+name);
                // 通过id获取数据（列的序号，从1开始）
                int id = rs.getInt(1);
                String name = rs.getString(2);
                System.out.println("id: " + id + ",name:" + name);
            }
        } catch (Exception e) {
            // 6. 释放资源
            if (rs != null) {
                try {
                    rs.close();
                } catch (Exception exc) {
                    exc.printStackTrace();
                }
            }
            if (stmt != null) {
                try {
                    stmt.close();
                } catch (Exception exc) {
                    exc.printStackTrace();
                }
            }
            if (conn != null) {
                try {
                    conn.close();
                } catch (Exception exc) {
                    exc.printStackTrace();
                }
            }
        }
    }

    // 增加密码列
    @Test
    public void addPasswordCol() {
        Connection conn = null;
        Statement stmt = null;
        try {
            // 1. 加载驱动
            Class.forName(driver);
            // 2. 获取连接对象
            conn = DriverManager.getConnection(url, username, password);
            // 3. 获取操作对象
            stmt = conn.createStatement();
            // 删除之前的数据
            // delete form t_user;
            // 4. 执行sql语句
            String sql = "alter table t_user add column password varchar(20) not null after name";
            int rows = stmt.executeUpdate(sql);
            // 5. 处理结果
            System.out.println("修改的行数 rows：" + rows);
        } catch (Exception e) {
            // 6. 释放资源
            if (stmt != null) {
                try {
                    stmt.close();
                } catch (Exception exc) {
                    exc.printStackTrace();
                }
            }
            if (conn != null) {
                try {
                    conn.close();
                } catch (Exception exc) {
                    exc.printStackTrace();
                }
            }
        }
    }

    // 登录
    @Test
    public void login() {
        Connection conn = null;
        Statement stmt = null;
        ResultSet rs = null;
        try {
            // 1. 加载驱动
            Class.forName(driver);
            // 2. 获取连接对象
            conn = DriverManager.getConnection(url, username, password);
            // 3. 获取操作对象
            stmt = conn.createStatement();
            // 用户输入的内容
            int userId = 1;
            // 用户输入的内容
            String userPwd = "'or '1'='1";
            // 4. 执行sql语句
            String sql = "select count(*) from t_user where id=" + userId + " and password='" + userPwd + "'";
            // String sql = "select count(*) from t_user where id=1 and password='tom'";
            System.out.println("sql:" + sql);
            rs = stmt.executeQuery(sql); // 注意这里执行的是查询语句
            // 5. 处理结果
            while (rs.next()) {
                // 返回值是数值
                int count = rs.getInt(1);
                System.out.println("count: " + count);
                if (count == 0) {
                    System.out.println("登录失败");
                } else {
                    System.out.println("登录成功");
                }
            }
        } catch (Exception e) {
            // 6. 释放资源
            if (rs != null) {
                try {
                    rs.close();
                } catch (Exception exc) {
                    exc.printStackTrace();
                }
            }
            if (stmt != null) {
                try {
                    stmt.close();
                } catch (Exception exc) {
                    exc.printStackTrace();
                }
            }
            if (conn != null) {
                try {
                    conn.close();
                } catch (Exception exc) {
                    exc.printStackTrace();
                }
            }
        }
    }

}